package com.example.studentcoursesysteam.Repository;

import com.example.studentcoursesysteam.Bean.Teacher;
import com.example.studentcoursesysteam.Vo.TeacherCourseMaxMinAvgVo;
import com.example.studentcoursesysteam.Vo.TeacherCourseVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface TeacherRepository extends JpaRepository<Teacher,Integer> {
    boolean existsByCode(String code);

    List<Teacher> findByCode(String code);
    List<Teacher> findByCodeAndPassword(String code, String password);

    @Transactional
    @Modifying
    @Query(value = "delete from teacher where code=:code",nativeQuery = true)
    Integer deleteTeacher(String code);

    @Query(value = "select t.code as teacherCode,t.name as teacherName, c.code as courseCode,c.name as courseName,c.credit as " +
            "courseCredit from course c, teacher t where c.teacher_code=t.code and t.code=:code",nativeQuery = true)
    List<TeacherCourseVo> selectTeacherCourse(String code);

    @Query(value = "select t.code as teacherCode,t.name as teacherName, c.code as courseCode,c.name as courseName,avg(sc.achievement) " +
            "as averageAchievement,max(sc.achievement) as maxAchievement,min(sc.achievement) as minAchievement " +
            "from course c, student_course sc,teacher t where sc.course_code=c.code " +
            "and c.teacher_code=t.code and t.code=:code group by c.code ",nativeQuery = true)
    List<TeacherCourseMaxMinAvgVo> selectTeacherCourseAchievement(String code);

    @Query(value = "select t.code as teacherCode,t.name as teacherName, c.code as courseCode,c.name as courseName,avg(sc.achievement) as averageAchievement,max(sc.achievement) as maxAchievement,min(sc.achievement) as minAchievement from course c, student_course sc,teacher t where c.teacher_code=t.code and c.name=:name ",nativeQuery = true)
    List<TeacherCourseMaxMinAvgVo> selectTeacherCourseAchievementName(String name);
}
